import pymysql

db = pymysql.connect(
    host='localhost',
    port=3306,
    database='warehouse',
    user='root',
    passwd='123456'
)

cursor = db.cursor()

class Goods:
    _id = 0
    _name = ""
    _inPrice = 0
    _outPrice = 0
    _inventory = 0

    def __init__(self,id,name,inPrice,outPrice,inventory):
        self._id = id
        self._name = name
        self._inPrice = inPrice
        self._outPrice = outPrice
        self._inventory = inventory

    @property
    def id(self):
        return self._id
    @id.setter
    def id(self,id):
        self._id = id

    @property
    def name(self):
        return self._name
    @name.setter
    def name(self,name):
        self._name = name

    @property
    def inPrice(self):
        return self._inPrice
    @inPrice.setter
    def inPrice(self,inPrice):
        self._inPrice = inPrice

    @property
    def outPrice(self):
        return self._outPrice
    @outPrice.setter
    def outPrice(self,outPrice):
        self._outPrice = outPrice

    @property
    def inventory(self):
        return self._inventory
    @inventory.setter
    def inventory(self,inventory):
        self._inventory = inventory

class InboundOrders:
    _id = 0
    _name = ""
    _intime = ""
    _inquantity = 0

    def __init__(self,id,name,intime,inquantity):
        self._id = id
        self._name = name
        self._intime = intime
        self._inquantity = inquantity

    @property
    def id(self):
        return self._id
    @id.setter
    def id(self,id):
        self._id = id

    @property
    def name(self):
        return self._name
    @name.setter
    def name(self,name):
        self._name = name

    @property
    def intime(self):
        return self._intime
    @intime.setter
    def intime(self,intime):
        self._intime = intime

    @property
    def inquantity(self):
        return self._inquantity
    @inquantity.setter
    def inquantity(self,inquantity):
        self._inquantity = inquantity

class OutboundOrders:
    _id = 0
    _name = ""
    _outtime = ""
    _outquantity = 0

    def __init__(self,id,name,outtime,outquantity):
        self._id = id
        self._name = name
        self._outtime = outtime
        self._outquantity = outquantity

    @property
    def id(self):
        return self._id
    @id.setter
    def id(self,id):
        self._id = id

    @property
    def name(self):
        return self._name
    @name.setter
    def name(self,name):
        self._name = name

    @property
    def outtime(self):
        return self._outtime
    @outtime.setter
    def outtime(self,outtime):
        self._outtime = outtime

    @property
    def outquantity(self):
        return self._outquantity
    @outquantity.setter
    def outquantity(self,outquantity):
        self._outquantity = outquantity


class Warehouse:
    def __init__(self):
        self.Goods = []
        self.InboundOrders = []
        self.OutboundOrders = []
        self.flashWH()

    def flashWH(self):
        self.importGoods()
        self.importInboundOrders()
        self.importOutboundOrders()

    def importGoods(self):
        self.Goods = []
        cursor.execute('SELECT count(*) FROM goods')
        num = cursor.fetchone()[0]
        cursor.execute('SELECT * FROM goods')
        for i in range(num):
            newGood = cursor.fetchone()
            Good = Goods(newGood[0],newGood[1],newGood[2],newGood[3],newGood[4])
            self.Goods.append(Good)
        db.commit()

    def importInboundOrders(self):
        self.InboundOrders = []
        cursor.execute('SELECT count(*) FROM inboundorders')
        num = cursor.fetchone()[0]
        cursor.execute('SELECT * FROM inboundorders')
        for i in range(num):
            newInboundOrder = cursor.fetchone()
            InboundOrder = InboundOrders(newInboundOrder[0],
                                         newInboundOrder[1],
                                         str(newInboundOrder[3]),
                                         newInboundOrder[2])
            self.InboundOrders.append(InboundOrder)
        db.commit()

    def importOutboundOrders(self):
        self.OutboundOrders = []
        cursor.execute('SELECT count(*) FROM outboundorders')
        num = cursor.fetchone()[0]
        cursor.execute('SELECT * FROM outboundorders')
        for i in range(num):
            newOutboundOrder = cursor.fetchone()
            OutboundOrder = OutboundOrders(newOutboundOrder[0],
                                         newOutboundOrder[1],
                                         str(newOutboundOrder[3]),
                                         newOutboundOrder[2])
            self.OutboundOrders.append(OutboundOrder)
        db.commit()

    def addGood(self,good):
        cursor.execute("insert into goods(gid,gname,ginprice,goutprice,ginventory) "
                       "values({},'{}',{},{},{})".format(good.id,good.name,good.inPrice,good.outPrice,good.inventory))
        db.commit()
        self.flashWH()

    def addInboundOrders(self, param):
        self.flashWH()

    def addOutboundOrders(self, param):
        self.flashWH()

    def deleteGood(self, id):
        cursor.execute("delete from goods where gid = {}".format(id))
        db.commit()
        self.flashWH()

    def deleteInboundOrders(self, id):
        cursor.execute("delete from inboundorders where ioid = {}".format(id))
        db.commit()
        self.flashWH()

    def deleteOutboundOrders(self, id):
        cursor.execute("delete from outboundorders where ooid = {}".format(id))
        db.commit()
        self.flashWH()

    def updateGood(self, good):
        cursor.execute("update goods set gname = '{}',ginprice = {}, goutprice = {},ginventory = {} where gid = {}".format(good.name,good.inPrice,good.outPrice,good.inventory,good.id))
        db.commit()
        self.flashWH()

    def updateInboundOrders(self, inboundOrder):
        self.flashWH()

    def updateOutboundOrders(self, outboundOrder):
        self.flashWH()

def getGoodsNumber():
    cursor.execute('SELECT count(*) FROM goods')
    return cursor.fetchone()[0]

def getInboundOrdersNumber():
    cursor.execute('SELECT count(*) FROM inboundorders')
    return cursor.fetchone()[0]

def getOutboundOrdersNumber():
    cursor.execute('SELECT count(*) FROM outboundorders')
    return cursor.fetchone()[0]

def clearDataBase():
    cursor.execute('truncate table goods')
    cursor.execute('truncate table inboundorders')
    cursor.execute('truncate table outboundorders')
    db.commit()

def selectGoods(name):
    cursor.execute("SELECT * FROM goods WHERE gname like '%{}%'".format(name))
    Good = cursor.fetchall()
    return Good

def selectInboundOrders(name):
    cursor.execute("SELECT * FROM inboundorders WHERE ioname like '%{}%'".format(name))
    InboundOrder = cursor.fetchall()
    return InboundOrder

def selectOutboundOrders(name):
    cursor.execute("SELECT * FROM outboundorders WHERE ooname like '%{}%'".format(name))
    OutboundOrder = cursor.fetchall()
    return OutboundOrder